﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Data;
using System.Windows.Forms;

namespace tpsmi1002
{
    class Portfolio
    {
        private static Portfolio instance;
        private Dictionary<int, Transaction> listTransactions;
        private CatalogueStock cs;
        private Database db;

        private Portfolio() 
        {
            cs = CatalogueStock.getInstance();
            db = Database.getInstance();

            listTransactions = new Dictionary<int, Transaction>();

            loadTransactionData();
        }

        public static Portfolio getInstance()
        {
            if (instance == null)
                instance = new Portfolio();

            return instance;
        }

        // charger les transactions dans la liste
        private void loadTransactionData()
        { 
            string query = "SELECT T.IDTRANSACTION, "+
	                               "T.IDSTOCK, "+
	                               "T.DATEACHAT, "+
	                               "T.PRIXACHAT, "+
	                               "T.QTE, "+
	                               "SUM(V.QTEVENTE) AS QTEVENDU, "+
	                               "S.NOM "+
                            "FROM (SELECT IDTRANSACTION, IDSTOCK, DATEACHAT, PRIXACHAT, QTE "+
	                              "FROM transaction "+
	                              "WHERE VENDU = 0 AND SUPPRIME = 0) T "+
	  
                            "INNER JOIN (SELECT IDSTOCK, NOM "+
			                            "FROM stock) S "+
	                            "ON S.IDSTOCK = T.IDSTOCK "+
      
                            "LEFT JOIN (SELECT QTEVENTE, IDTRANSACTION "+
		                            "FROM vente " +
		                            "WHERE SUPPRIME = 0) V " +
	                            "ON T.IDTRANSACTION = V.IDTRANSACTION "+
      
                            "GROUP BY T.IDTRANSACTION, S.NOM, T.IDSTOCK, T.DATEACHAT, T.PRIXACHAT, T.QTE "+
                            "ORDER BY T.DATEACHAT, S.NOM, T.PRIXACHAT; ";
	  
            
            DataSet ds = db.ExecuteDataSet(query);

            // checker la quantite si elle est invalide, ne pas afficher ou si negative, faire dequoi yo yo

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                int quantityRestante = (row[6] == DBNull.Value) ? (int)row[5] : (int)row[5] - (int)int.Parse(row[6].ToString());

                listTransactions.Add((int)row[0], new Transaction((int)row[0], cs.getListStock()[(int)row[2]], (DateTime)row[3], (double)row[4], quantityRestante));
            }
        }

        public Dictionary<int, Transaction> getListTransaction()
        {
            return this.listTransactions;
        }

        // ajout de transaction
        public void addTransaction(int _quantity, Stock _stock, DateTime _purchaseDate, double _purchasePrice)
        {
            string query = "INSERT INTO TRANSACTION (IDSTOCK, DATEACHAT, PRIXACHAT, QTE, VENDU, SUPPRIME) VALUES (" + _stock.IdStock.ToString() + ",TO_DATE('" + _purchaseDate.Year + "-" + _purchaseDate.Month + "-" + _purchaseDate.Day + "', 'YYYY-MM-DD')," + _quantity.ToString() + "," + _purchasePrice.ToString().Replace(',', '.') + ", 0, 0)";

            int rowaffected = db.ExecuteNonQuery(query);

            query = "SELECT MAX(IDTRANSACTION) + 1 AS LASTID FROM TRANSACTION";
            DataSet ds = db.ExecuteDataSet(query);

            int newid = (int)int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());

            listTransactions.Add(newid, new Transaction(newid, _stock, _purchaseDate, _purchasePrice, _quantity));
        }

        // modification de transaction
        public void saveModification(Transaction _transaction, int _quantity, Stock _stock, DateTime _purchaseDate, double _purchasePrice)
        {
            int newQty = _quantity - _transaction.Quantity;

            string query = "UPDATE TRANSACTION SET DATEACHAT=TO_DATE('" + _purchaseDate.Year + "-" + _purchaseDate.Month + "-" + _purchaseDate.Day + "', 'YYYY-MM-DD'), " +
                            "QTE=(" + newQty.ToString().Replace(",", ".") + " + (SELECT QTE FROM TRANSACTION WHERE IDTRANSACTION=" + _transaction.IdTransaction.ToString() +")), " +
                            "PRIXACHAT=" + _purchasePrice.ToString().Replace(',', '.') + " " +
                            "WHERE IDTRANSACTION=" + _transaction.IdTransaction.ToString();
            
            int affectedrow = db.ExecuteNonQuery(query);

            listTransactions.Remove(_transaction.IdTransaction);
            listTransactions.Add(_transaction.IdTransaction, new Transaction(_transaction.IdTransaction, _stock, _purchaseDate, _purchasePrice, _quantity));
        }

        // vente d'actions
        public void sellTransaction(Transaction _transaction, double _prixVente, int _qteVente)
        {
            string query = "INSERT INTO VENTE (IDTRANSACTION, QTEVENTE, PRIXVENTE, DATEVENTE, SUPPRIME) VALUES (" + _transaction.IdTransaction.ToString() + "," + _qteVente.ToString() + "," + _prixVente.ToString() + ", TO_DATE('" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + "', 'YYYY-MM-DD'), 0)";
            int affected = db.ExecuteNonQuery(query);

            if (_transaction.Quantity == _qteVente)
            {
                query = "UPDATE TRANSACTION SET VENDU = 1 WHERE IDTRANSACTION = " + _transaction.IdTransaction.ToString();
                int idtransaction = db.ExecuteNonQuery(query);

                listTransactions.Remove(_transaction.IdTransaction);
            }
            else
            {
                listTransactions[_transaction.IdTransaction].Quantity = _transaction.Quantity - _qteVente;
            }
        }

        // suppression d'une transaction
        public void deleteTransaction(Transaction _t)
        {
            string query = "UPDATE TRANSACTION SET SUPPRIME = 1 WHERE IDTRANSACTION = " + _t.IdTransaction;
            int affected = db.ExecuteNonQuery(query);

            listTransactions.Remove(_t.IdTransaction);
        } 
    }
}
